package com.tcfvic.server;

import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.tcfvic.client.ReportService;

public class ReportServiceImpl extends AbstractReportService implements
		ReportService {

	private static final long serialVersionUID = -1983872408127441938L;
	private static final SimpleDateFormat day = new SimpleDateFormat("dd");
	private static final SimpleDateFormat month = new SimpleDateFormat("MM");
	private static final SimpleDateFormat year = new SimpleDateFormat("yyyy");
	private static final SimpleDateFormat dateSDF = new SimpleDateFormat(
			"dd/MM/yyyy");

	public static void main(String... args) throws Exception {
		ReportServiceImpl i = new ReportServiceImpl();
		i.getCompaniesReport(0, "", "");

	}

	@Override
	public String getAllPersonDetails() {
		return generateExcelFile("SELECT * FROM Person p ;", getFileName());
		// return generateExcelFile(
		// "SELECT * FROM Person p left outer join Employmentstatus es "
		// + "on p.id = es.person_id;", getFileName());
	}

	@Override
	public String getAllPersonWithTrainingDetails() {
		return generateExcelFile(
				"SELECT p.*, c.*, pc.* FROM personcourse pc left outer join person p on pc.person_id = p.id left outer join course c on pc.course_id = c.id",
				getFileName());
	}

	public String getWorkerTabQuery(List<String> criteria) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();

		StringBuffer where = new StringBuffer();
		if (criteria != null && criteria.size() > 0) {
			for (String s : criteria) {
				where.append(s);
			}
			where.append(" and ");
		}

		List<String> selects = new ArrayList<String>();
		selects.add("p.unionMemberNo as 'Union Member No.'");
		selects.add("p.gender as Gender");
		selects.add("p.surname as Surname");
		selects.add("p.firstname as 'Given names'");
		selects.add("p.dateofbirth as 'Date of Birth'");
		selects.add("a.street as 'Street line 1'");
		selects.add("a.suburb as Suburb");
		selects.add("a.state as State");
		selects.add("a.postcode as Postcode");
		selects.add("p.email as Email");
		selects.add("p.landline as 'Phone 1'");
		selects.add("p.mobile as 'Phone 2'");
		selects.add("p.notes as Notes");
		selects.add("ed.employeenumber as 'Employee number'");
		selects.add("ed.Position as Position");
		selects.add("ed.Status as Status");
		selects.add("ed.State as 'Company State'");
		selects.add("wc.name as Company");
		selects.add("ed.sector as 'TCF Sector'");
		selects.add("wc.site as Site");
		selects.add("ed.dateStarted as 'Company start date'");
		selects.add("ed.dateleaving as 'Company finish date'");
		selects
				.add("round((datediff(ed.dateleaving, ed.dateStarted) / 365), 1) as 'Years of service'");
		selects.add("p.countryoforigin as 'Country of origin'");
		selects.add("p.dateofmigration as 'Date of migration'");
		selects.add("p.firstlanguage as 'First language'");
		selects.add("p.formaledhighlevel as 'Highest level'");
		selects.add("p.formaledcountry as 'Country obtained'");
		selects.add("c.name as 'Course name'");
		selects.add("c.courseType as 'Course type'");
		selects.add("r.name as RTO");
		selects.add("r.campus as 'RTO campus'");
		selects.add("pc.trainingdate as 'Course start date'");
		selects.add("pc.finishdate as 'Course finish date'");
		selects.add("pc.completed as Completed");
		selects.add("e.date as 'Date of expense'");
		selects.add("c.name as 'Related course'");
		selects.add("e.description as Description");
		selects.add("fs.sourcename as 'Funding Source'");
		selects.add("e.amount as Amount");
		// selects.add("es.reasonforleaving as 'Reason for leaving'");
		String selectsAsString = selects.toString().replace("[", "").replace(
				"]", "");

		columnForQueries.add(stripSQL(selects));

		String sql = "SELECT "
				+ selectsAsString
				+ " "
				+ "FROM person p "
				+ " left outer join (SELECT * FROM (SELECT * FROM personaddress p order by address_id desc ) p group by person_id) pa on p.id = pa.person_id "
				+ " inner join address a on pa.address_id = a.id "
				+ " left outer join employmentdetails ed on p.id = ed.person_id "
				+ " left outer join workercompany wc on wc.id = ed.workercompany_id "
				+ " left outer join personcourse pc on p.id = pc.person_id "
				+ " left outer join course c on pc.course_id = c.id "
				+ " left outer join rto r on pc.rto_id = r.id "
				+ " left outer join expense e on p.id = e.person_id "
				+ " left outer join personjsp pj on p.id = pj.person_id "
				+ " left outer join jsp j on pj.jsp_id = j.id "
				+ " left outer join employmentstatus es on es.person_id = p.id "
				+ " left outer join fundingsource fs on fs.id = fundingsource_id ";

		return generateExcelFile(new String[] { sql }, getFileName(),
				columnForQueries);
	}

	private List<String> stripSQL(List<String> selects) {
		List<String> noSQLInSelects = new ArrayList<String>();
		for (String s : selects) {
			noSQLInSelects.add(s.substring(s.indexOf(" as ") + 4).replace("'",
					""));
		}
		return noSQLInSelects;
	}

	public String getCTETrainingAndExpenseSummary(List<String> criteria) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Course");
		columns.add("Cost");
		columns.add("Payment");
		columns.add("No of people");
		columnForQueries.add(columns);
		columns = new ArrayList<String>();
		columns.add("Expenses");
		columns.add("Cost");
		columns.add("Payment");
		columns.add("No of people");
		columnForQueries.add(columns);

		StringBuffer where = new StringBuffer();
		if (criteria != null && criteria.size() > 0) {
			for (String s : criteria) {
				where.append(s);
			}
			where.append(" and ");
		}

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries.add("SELECT c.name as Course, sum(e.amount) as Cost, "
				+ "f.sourcename as Payment, count(*) as 'No of people' "
				+ "FROM expense e,course c, fundingsource f " + "WHERE "
				+ where + "c.name = e.relatedCourse "
				+ "AND f.id = e.fundingsource_id "
				+ "AND (units is null or units = 'Dollars') "
				+ "GROUP BY c.name");
		sqlQueries
				.add("SELECT e.description as Expenses, sum(e.amount) as Cost, "
						+ "f.sourcename as Payment, count(*) as 'No of people' "
						+ "FROM expense e,course c, fundingsource f "
						+ "WHERE "
						+ where
						+ "c.name = e.relatedCourse "
						+ "AND f.id = e.fundingsource_id "
						+ "AND (units is null or units = 'Dollars') "
						+ "GROUP BY e.description");
		ReportRequest reportRequest = new ReportRequest("Sheet 1", sqlQueries,
				columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getCompInfoReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Years Worked");
		columns.add("Employee No.");
		columns.add("Start Date");
		columns.add("Date Finished");
		columns.add("Position");
		columns.add("Status");
		columns.add("TCF Sector");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ "p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ "DATE_FORMAT(FROM_DAYS(TO_DAYS(dateleaving)-TO_DAYS(datestarted)), '%Y')+0 as 'Years Worked',  "
						+ "e.employeenumber as 'Employee No.', datestarted as 'Start Date', dateleaving as 'Date Finished', "
						+ "e.position as Position, e.status as Status, e.sector as 'TCF Sector' "
						+ "From "
						+ "person p left join employmentdetails e on e.person_id = p.id "
						+ "left join workercompany wc on wc.id = e.workercompany_id "
						+ where);
		ReportRequest reportRequest = new ReportRequest(
				"Company Information Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getEmpStatusReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Employment Status");
		columns.add("Reason for Unemployment");
		columns.add("Type of Work");
		columns.add("Hours of Work");
		columns.add("Role / Position");
		columns.add("Emp. company");
		columns.add("Industry");
		columns.add("Size of Company");
		columns.add("Suburb");
		columns.add("State");
		columns.add("Start Date");
		columns.add("End Date");
		columns.add("Work Conditions");
		columns.add("Joined a Union");
		columns.add("Reason for leaving");
		columns.add("Notes (Employment)");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ "p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ "empstatus as 'Employment Status', needwantmorehours as 'Reason for Unemployment', "
						+ "typeofwork as 'Type of Work', hoursofwork as 'Hours of Work', jobrole as 'Role / Position', "
						+ "nameofemployer as 'Emp. company', i.name as 'Industry', sizeofcompany as 'Size of Company', "
						+ "es.suburb as 'Suburb', es.state as 'State', startdate as 'Start Date', finishDate as 'End Date', "
						+ "workingconditions as 'Work Conditions', unionmember as 'Joined a Union', reasonforleaving as 'Reason for leaving', "
						+ "jobno as 'Notes (Employment)' "
						+ "From "
						+ "person p left join employmentdetails e on e.person_id = p.id "
						+ "left outer join employmentstatus es on es.person_id = p.id "
						+ "left outer join workercompany wc on wc.id = e.workercompany_id "
						+ "left outer join industry i on i.id = es.industry "
						+ where);
		ReportRequest reportRequest = new ReportRequest(
				"Employment Status Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getJSAPRegReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Did not register");
		columns.add("JSAP");
		columns.add("Job seeker ID");
		columns.add("Date of registration");
		columns.add("Registration stream");
		columns.add("Street");
		columns.add("Suburb");
		columns.add("State");
		columns.add("Postcode");
		columns.add("Phone");
		columns.add("Website");
		columns.add("Email");
		columns.add("Notes (JSAP)");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ "p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ "registered as 'Did not register', j.name as 'JSAP', jspid as 'Job seeker ID', "
						+ "dateofregistration as 'Date of registration', registrationstream as 'Registration stream', "
						+ "j.Street as 'Street', j.suburb as Suburb, j.state as State, j.postcode as Postcode, "
						+ "j.phone as Phone, j.website as Website, j.street2 as Email, j.notes as Notes "
						+ "From "
						+ "person p left join employmentdetails e on e.person_id = p.id "
						+ "left outer join workercompany wc on wc.id = e.workercompany_id "
						+ "left outer join personjsp pj on p.id = pj.person_id "
						+ "left outer join jsp j on j.id = pj.jsp_id " + where);
		ReportRequest reportRequest = new ReportRequest(
				"Worker JSAP Registration Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getJSAPContDetailsReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("JSAP");
		columns.add("Consultant name");
		columns.add("Consultant email");
		columns.add("Consultant phone");
		columns.add("Consultant mobile");
		columns.add("Manager name");
		columns.add("Manager email");
		columns.add("Manager phone");
		columns.add("Manager mobile");
		columns.add("Notes (JSAP Worker)");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ "p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ "j.name as JSAP, consultantsname as 'Consultant name', consultantsemail as 'Consultant email', "
						+ "consultantphone as 'Consultant phone', consultantmobile as 'Consultant mobile', "
						+ "managersname as 'Manager name', managersemail as 'Manager email', managerphone as 'Manager phone', "
						+ "managermobile as 'Manager mobile', pj.notes as 'Notes (JSAP Worker)' "
						+ "From "
						+ "person p left join employmentdetails e on e.person_id = p.id "
						+ "left outer join workercompany wc on wc.id = e.workercompany_id "
						+ "left outer join personjsp pj on p.id = pj.person_id "
						+ "left outer join jsp j on j.id = pj.jsp_id " + where);
		ReportRequest reportRequest = new ReportRequest(
				"JSAP Contact Details Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getPIInfoReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Age");
		columns.add("Home Address");
		columns.add("Suburb");
		columns.add("State");
		columns.add("Postcode");
		columns.add("Phone");
		columns.add("Mobile");
		columns.add("Email");
		columns.add("Union Member");
		columns.add("Country of Origin");
		columns.add("Date of Migration");
		columns.add("First Language");
		columns.add("Highest level education");
		columns.add("Country obtained");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);
		if (where.length() > 0) {
			where.append(" and ");
		} else {
			where.append(" where ");
		}
		
		where.append(" a.id = (select address_id from personaddress "
				+ " inpa where inpa.person_id = p.id "
				+ " order by dateofarrival desc limit 1) "
				+ " and p.display is null ");

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ " p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ " DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dateofbirth)), '%Y')+0 AS Age, title as Title, "
						+ " a.street as 'Home Address', a.suburb as Suburb, a.state as State, a.postcode as Postcode, "
						+ " p.landline as Phone, p.mobile as Mobile, p.email as Email, p.unionMemberNo as 'Union Member', "
						+ " p.countryoforigin as 'Country of Origin', p.dateofmigration as 'Date of Migration', "
						+ " p.firstlanguage as 'First Language', p.formalEdHighlevel as 'Highest level education', "
						+ " p.formaledcountry as 'Country obtained' "
						+ " From "
						+ " person p left join employmentdetails e on e.person_id = p.id "
						+ " left join workercompany wc on wc.id = e.workercompany_id "
						+ " left join personaddress pa on pa.person_id = p.id "
						+ " left join address a on pa.address_id = a.id "
						+ where);
		ReportRequest reportRequest = new ReportRequest(
				"Personal Information Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getCourseInfoReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Course Name");
		columns.add("Course Type");
		columns.add("RTO");
		columns.add("RTO Campus");
		columns.add("Street");
		columns.add("Suburb");
		columns.add("State");
		columns.add("Postcode");
		columns.add("RTO phone");
		columns.add("RTO fax");
		columns.add("Website");
		columns.add("Contact");
		columns.add("Position");
		columns.add("Contact phone");
		columns.add("Contact mobile");
		columns.add("Contact email");
		columns.add("Notes (RTO)");
		columns.add("Start Date");
		columns.add("End Date");
		columns.add("Cost of course");
		columns.add("Funding Source");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);
		if (where.length() > 0) {
			where.append(" and ");
		} else {
			where.append(" where ");
		}
		where.append(" p.display is null ");

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ " p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ " c.name as 'Course Name', c.coursetype as 'Course Type', r.name as RTO, "
						+ " r.campus as 'RTO Campus', pc.trainingdate as 'Start Date', pc.finishdate as 'End Date', "
						+ " ee.amount as 'Cost of course', fs.sourceName as 'Funding Source', "
						+ " r.street1 as Street, r.suburb as Suburb, r.state as State, r.postcode as Postcode, "
						+ " r.rtoPhone as 'RTO phone', r.rtofax as 'RTO fax', r.website as Website, r.contactName as Contact, "
						+ " r.position as Position, r.tel1 as 'Contact phone', r.tel2 as 'Contact mobile', "
						+ " r.email as 'Contact email', r.notes as 'Notes (RTO)' "
						+ " From "
						+ " person p left join employmentdetails e on e.person_id = p.id "
						+ " left join workercompany wc on wc.id = e.workercompany_id "
						+ " left join personcourse pc on p.id = pc.person_id "
						+ " left join expense ee on (pc.course_id = ee.relatedcourse and ee.person_id = p.id) "
						+ " left join course c on c.id = pc.course_id "
						+ " left join fundingsource fs on ee.fundingsource_id = fs.id "
						+ " left join rto r on r.id = pc.rto_id "
						+ where
						+ " group by pc.rto_id, c.id, p.id, pc.trainingdate, pc.finishdate "
						+ " order by dateleaving, wc.name, wc.site, p.firstname, p.surname ");
		ReportRequest reportRequest = new ReportRequest(
				"Course Information Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getCompaniesReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("Brand");
		columns.add("Street");
		columns.add("Suburb");
		columns.add("State");
		columns.add("Postcode");
		columns.add("Site closure date");
		columns.add("Company phone");
		columns.add("Company contact");
		columns.add("Contact position");
		columns.add("Contact phone");
		columns.add("Contact mobile");
		columns.add("Contact email");
		columns.add("Closure reason");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString("wc.dateofclosure", year,
				company, site);
		if (where.length() > 0) {
			where.append(" and ");
		} else {
			where.append(" where ");
		}
		where.append(" p.display is null ");

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT wc.dateofclosure as Year, wc.name as Company, wc.site as Site, Brand as Brand, "
						+ " addressline1 as Street, suburb as Suburb, state as State, postcode as Postcode, "
						+ " dateofclosure as 'Site closure date', phone as 'Company phone', contactperson as 'Company contact', "
						+ " contactposition as 'Contact position', contactph1 as 'Contact phone', contactph2 as 'Contact mobile', "
						+ " contactph3 as 'Contact email', reasonforredundancies as 'Closure reason' "
						+ " From "
						+ " workercompany wc "
						+ where
						+ " order by wc.name, wc.site ");
		ReportRequest reportRequest = new ReportRequest(
				"Course Information Report", sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	public String getExpenseReport(int year, String company, String site) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("Year");
		columns.add("Company");
		columns.add("Site");
		columns.add("First Name");
		columns.add("Surname");
		columns.add("Birth Date");
		columns.add("Gender");
		columns.add("Description");
		columns.add("Total");
		columns.add("Funding Source");
		columnForQueries.add(columns);

		StringBuffer where = generateWhereString(year, company, site);
		if (where == null || where.length() == 0) {
			where = new StringBuffer(" where ");
		} else {
			where.append(" and ");
		}
		where.append(" description not like 'Course fees' ");
		where.append(" and p.display is null ");


		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries
				.add("SELECT dateleaving as Year, wc.name as Company, wc.site as Site, p.firstname as 'First Name', "
						+ " p.surname as Surname, dateofbirth as 'Birth Date', gender as Gender, "
						+ " ee.Description as 'Description', ee.amount as Total, fs.sourceName as 'Funding Source' "
						+ " From "
						+ " person p left join employmentdetails e on e.person_id = p.id "
						+ " left join workercompany wc on wc.id = e.workercompany_id "
						+ " left join expense ee on p.id = ee.person_id "
						+ " left join fundingsource fs on fs.id = ee.fundingsource_id "
						+ where);
		ReportRequest reportRequest = new ReportRequest("Expense Report",
				sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);
		return generateExcelFile(requests, getFileName());
	}

	private StringBuffer generateWhereString(int year, String company,
			String site) {
		return generateWhereString("dateleaving", year, company, site);
	}

	private StringBuffer generateWhereString(String yearColumnName, int year,
			String company, String site) {
		StringBuffer where = new StringBuffer();
		if (year > 0) {
			where.append(" where ");
			where.append(" " + yearColumnName + " > str_to_date('" + year
					+ "','%Y')");
			where.append(" and " + yearColumnName + " < str_to_date('"
					+ (year + 1) + "','%Y')");
		}
		if (company != null && company.length() > 0) {
			if (where.length() > 0) {
				where.append(" and ");
			} else {
				where.append(" where ");
			}
			where.append(" wc.name = '" + company + "'");
		}
		if (site != null && site.length() > 0) {
			if (where.length() > 0) {
				where.append(" and ");
			} else {
				where.append(" where ");
			}
			where.append(" wc.site = '" + site + "'");
		}
		return where;
	}

	@Override
	public String getAgedOverXYears(int x) throws Exception {
		long currentTime = System.currentTimeMillis();
		String dayValue = day.format(new Date(currentTime));
		String monthValue = month.format(new Date(currentTime));
		String yearValue = String.valueOf(Integer.parseInt(year
				.format(new Date(currentTime)))
				- x);
		Date date = new Date(dateSDF.parse(
				dayValue + "/" + monthValue + "/" + yearValue).getTime());
		return generateExcelFile("select * from Person where dateofbirth < '"
				+ date + "'", getFileName());
	}

	@Override
	public String getPeopleInSuburb(String suburb) throws Exception {
		return generateExcelFile(
				"select * from person p join address a on p.id = a.id "
						+ "where a.suburb = '" + suburb + "'", getFileName());
	}

	public String getPeopleDynamicQuery(List<String> criteria) {
		StringBuffer where = new StringBuffer();
		if (criteria != null) {
			for (String entry : criteria) {
				where.append(" " + entry + " ");
			}
		}
		String sql = "select p.*, c.name as coursename, wc.name as companyname, wc.site "
				+ "from "
				+ "person p left join employmentdetails ed on p.id = ed.person_id "
				+ "left join personcourse pc on pc.person_id = p.id "
				+ "left join course c on c.id = pc.course_id "
				+ "left join workercompany wc on wc.id = ed.workercompany_id "
				+ "left join rto r on r.id = pc.rto_id ";
		if (criteria != null && criteria.size() > 0) {
			sql = sql + " where " + where;
		}

		return generateExcelFile(sql, getFileName());
	}

	public String getPeopleTrainingQuery(List<String> criteria) {
		StringBuffer where = new StringBuffer();
		if (criteria != null) {
			for (String entry : criteria) {
				where.append(" " + entry + " ");
			}
		}
		String sql = "select p.*, c.name as coursename, wc.name as companyname, wc.site, fs.sourcename "
				+ "from "
				+ "person p left join employmentdetails ed on p.id = ed.person_id "
				+ "left join personcourse pc on pc.person_id = p.id "
				+ "left join course c on c.id = pc.course_id "
				+ "left join workercompany wc on wc.id = ed.workercompany_id "
				+ "left join rto r on r.id = pc.rto_id "
				+ "left join expense e on (e.person_id = p.id and e.relatedCourse = c.name) "
				+ "left join fundingsource fs on fs.id = e.fundingsource_id "
				+ "where pc.course_id is not null ";
		if (criteria != null && criteria.size() > 0) {
			sql = sql + " and " + where;
		}

		return generateExcelFile(sql, getFileName());
	}

	private String getFileName() {
		return System.currentTimeMillis() + ".xls";
	}

	public String getPeopleJSPQuery(List<String> criteria) {
		StringBuffer where = new StringBuffer();
		if (criteria != null) {
			for (String entry : criteria) {
				where.append(" " + entry + " ");
			}
		}
		String sql = "select p.surname, p.firstname, j.* "
				+ "from "
				+ "person p left join employmentdetails ed on p.id = ed.person_id "
				+ "left join personcourse pc on pc.person_id = p.id "
				+ "left join course c on c.id = pc.course_id "
				+ "left join workercompany wc on wc.id = ed.workercompany_id "
				+ "left join rto r on r.id = pc.rto_id "
				+ "left join expense e on (e.person_id = p.id and e.relatedCourse = c.name) "
				+ "left join fundingsource fs on fs.id = e.fundingsource_id "
				+ "left join personjsp pj on pj.person_id = p.id "
				+ "left join jsp j on j.id = pj.jsp_id "
				+ "where j.id is not null ";
		if (criteria != null && criteria.size() > 0) {
			sql = sql + " and " + where;
		}

		return generateExcelFile(sql, getFileName());
	}

	public String getPeopleRegisteredForWork(List<String> criteria) {
		StringBuffer where = new StringBuffer();
		if (criteria != null) {
			for (String entry : criteria) {
				where.append(" " + entry + " ");
			}
		}
		String sql = "select p.surname, p.firstname, j.* "
				+ "from "
				+ "person p left join employmentdetails ed on p.id = ed.person_id "
				+ "left join workercompany wc on wc.id = ed.workercompany_id "
				+ "left join personjsp pj on pj.person_id = p.id "
				+ "left join jsp j on j.id = pj.jsp_id "
				+ "left join employmentstatus es on es.person_id = p.id "
				+ "where es.id is not null ";
		if (criteria != null && criteria.size() > 0) {
			sql = sql + " and " + where;
		}

		return generateExcelFile(sql, getFileName());
	}

	public List<String> getPeopleRetrenchedColumnNames() {
		List<String> columnNames = new ArrayList<String>();
		String sql = "select p.* from person p ";
		columnNames.addAll(getPersonColumnNames("p.", sql));
		return columnNames;
	}

	// SELECT wc.site, p.firstname, p.surname, ed.employeenumber, c.name,
	// c.courseType, r.name,
	// pc.trainingdate, pc.finishdate, e.amount, fs.sourceName
	// FROM
	// person p left join employmentdetails ed on p.id = ed.person_id
	// left join workercompany wc on ed.workercompany_id = wc.id
	// left join personcourse pc on pc.person_id = p.id
	// left join course c on c.id = pc.course_id
	// left join rto r on r.id = pc.rto_id
	// left join expense e on (e.relatedcourse = c.name and e.person_id = p.id)
	// left join fundingsource fs on fs.id = e.fundingsource_id

	public String getSummaryReport(List<String> criteria) {
		List<List<String>> columnForQueries = new ArrayList<List<String>>();

		StringBuffer where = new StringBuffer();
		if (criteria != null && criteria.size() > 0) {
			for (String s : criteria) {
				where.append(s);
			}
			where.append(" and ");
		}

		List<String> selects = new ArrayList<String>();

		selects.add("wc.site as Site");
		selects.add("p.firstname as 'First Name'");
		selects.add("p.surname as Surname");
		selects.add("ed.employeenumber as 'Employee No'");
		selects.add("c.name as 'Course Name'");
		selects.add("c.courseType as 'Course Type'");
		selects.add("r.name as RTO");
		selects.add("pc.trainingdate as 'Start Date'");
		selects.add("pc.finishdate as 'End Date'");
		selects.add("e.amount as 'Cost of course'");
		selects.add("fs.sourceName as 'Funding Source'");
		String selectsAsString = selects.toString().replace("[", "").replace(
				"]", "");
		columnForQueries.add(stripSQL(selects));
		String sql = "SELECT "
				+ selectsAsString
				+ " "
				+ "FROM "
				+ "person p left join employmentdetails ed on p.id = ed.person_id "
				+ "left join workercompany wc on ed.workercompany_id = wc.id "
				+ "left join personcourse pc on pc.person_id = p.id "
				+ "left join course c on c.id = pc.course_id "
				+ "left join rto r on r.id = pc.rto_id "
				+ "left join expense e on (e.relatedcourse = c.name and e.person_id = p.id) "
				+ "left join fundingsource fs on fs.id = e.fundingsource_id ";

		List<String> sqlQueries = new ArrayList<String>();
		sqlQueries.add(sql);
		ReportRequest reportRequest = new ReportRequest("Workers and Training",
				sqlQueries, columnForQueries);
		List<ReportRequest> requests = new ArrayList<ReportRequest>();
		requests.add(reportRequest);

		columnForQueries = new ArrayList<List<String>>();
		selects = new ArrayList<String>();
		selects.add("wc.site as Site");
		selects.add("p.firstname as 'First Name'");
		selects.add("p.surname as Surname");
		selects.add("ed.employeenumber as 'Employee No'");
		selects.add("e.description as Description");
		selects.add("e.amount as Total");
		selects.add("f.sourcename as 'Funding Source'");
		selectsAsString = selects.toString().replace("[", "").replace("]", "");
		columnForQueries.add(stripSQL(selects));
		sql = "SELECT "
				+ selectsAsString
				+ " "
				+ "FROM "
				+ "person p left outer join employmentdetails ed on p.id = ed.person_id "
				+ "left outer join workercompany wc on wc.id = ed.workercompany_id "
				+ "left outer join expense e on p.id = e.person_id "
				+ "left outer join fundingsource f on f.id = e.fundingsource_id ";

		sqlQueries = new ArrayList<String>();
		sqlQueries.add(sql);
		reportRequest = new ReportRequest("Workers and Expenses", sqlQueries,
				columnForQueries);
		requests.add(reportRequest);

		columnForQueries = new ArrayList<List<String>>();
		selects = new ArrayList<String>();
		selects.add("c.name as 'Course Name'");
		selects.add("c.courseType as 'Course Type'");
		selects.add("sum(e.amount) as 'Cost of course'");
		selects.add("fs.sourceName as 'Funding Source'");
		selects.add("count(distinct(e.person_id)) as 'No of People'");
		selectsAsString = selects.toString().replace("[", "").replace("]", "");
		columnForQueries.add(stripSQL(selects));
		sql = "SELECT " + selectsAsString + " " + "FROM "
				+ "course c join expense e on e.relatedCourse = c.name "
				+ "join fundingsource fs on e.fundingsource_id = fs.id "
				+ "group by c.name, c.courseType, e.fundingsource_id ";
		sqlQueries = new ArrayList<String>();
		sqlQueries.add(sql);
		reportRequest = new ReportRequest("Courses Summary", sqlQueries,
				columnForQueries);
		requests.add(reportRequest);

		// first query, same sheet
		columnForQueries = new ArrayList<List<String>>();
		sqlQueries = new ArrayList<String>();
		selects = new ArrayList<String>();
		selects.add("e.description as 'Description'");
		selects.add("sum(amount) as 'Cost'");
		selects.add("fs.sourceName as 'Funding source'");
		selects.add("count(*) as 'No of People'");
		selectsAsString = selects.toString().replace("[", "").replace("]", "");
		columnForQueries.add(stripSQL(selects));
		sql = "SELECT "
				+ selectsAsString
				+ " "
				+ "FROM "
				+ "expense e join fundingsource fs on e.fundingsource_id = fs.id "
				+ "where e.description = 'Course fees' "
				+ "group by e.fundingsource_id ";
		sqlQueries.add(sql);
		// second query, same sheet
		selects = new ArrayList<String>();
		selects.add("e.description as 'Description'");
		selects.add("sum(amount) as 'Cost'");
		selects.add("fs.sourceName as 'Funding source'");
		selects.add("count(*) as 'No of People'");
		selectsAsString = selects.toString().replace("[", "").replace("]", "");
		columnForQueries.add(stripSQL(selects));
		sql = "SELECT "
				+ selectsAsString
				+ " "
				+ "FROM "
				+ "expense e join fundingsource fs on e.fundingsource_id = fs.id "
				+ "where e.description <> 'Course fees' "
				+ "group by e.description, e.fundingsource_id; ";
		sqlQueries.add(sql);

		reportRequest = new ReportRequest("Training & Expenses Costs",
				sqlQueries, columnForQueries);
		requests.add(reportRequest);

		// SELECT c.name, c.courseType, e.fundingsource_id, sum(e.amount),
		// count(distinct(e.person_id))
		// FROM course c join expense e on e.relatedCourse = c.name
		// where c.name like 'My everything%'
		// group by c.name, c.courseType, e.fundingsource_id;
		//
		//
		// select c.name, c.courseType, e.amount, f.sourcename, count(c.name)
		// from person p
		// left outer join expense e on p.id = e.person_id
		// left outer join fundingsource f on f.id = e.fundingsource_id
		// left outer join personcourse pc on pc.person_id = p.id
		// left outer join course c on c.id = pc.course_id
		// where surname = 'Humbert'
		// group by c.name, c.courseType, f.id;
		//
		//
		// select pc.person_id, count(*) from personcourse pc
		// left outer join course c on pc.course_id = c.id
		// where c.name = 'My everything';
		//
		// SELECT * FROM person p where id = 1640;

		return generateExcelFile(requests, getFileName());
	}

	@Override
	public String getAllData() {
		return generateAllDataHorizontal(getFileName());
	}

	@Override
	public String getAllDataVertical() {
		return generateAllDataVertical(getFileName());
	}

	@Override
	public String getJSPData() {
		return generateExcelFile(
				"SELECT wc.name, wc.site, p.firstname, p.surname, j.*, pj.*, pj.notes as 'Person Notes' "
						+ "FROM "
						+ "person p left outer join personjsp pj on p.id = pj.person_id "
						+ "left outer join jsp j on pj.jsp_id = j.id "
						+ "left outer join employmentdetails ed on ed.person_id = p.id "
						+ "left outer join workercompany wc  on wc.id = ed.workercompany_id ",
				getFileName());
	}

	@Override
	public String getEmploymentData() {
		return generateExcelFile(
				"SELECT wc.name as Company, wc.site as Site, p.firstname, p.surname, e.*, i.name as 'Industry name' FROM "
						+ "person p left outer join employmentstatus e on p.id = e.person_id "
						+ "left outer join employmentdetails ed on ed.person_id = p.id "
						+ "left outer join workercompany wc on wc.id = ed.workercompany_id "
						+ "left outer join industry i on i.id = e.industry ",
				getFileName());
	}

}
